/**
 *
 */
package com.honeybees.framework.mybatis.util;

import java.sql.SQLException;
import java.text.DateFormat;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;

import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.jdbc.BadSqlGrammarException;

/**
 * <dl>
 * <dt><b> 解析 {@link BoundSql}，得到SQL语句 </b></dt>
 * <p>
 * <dd>功能描述</dd>
 * </dl>
 * <p>
 * Copyright (C) All rights reserved.
 * </p>
 *
 * @author 李远明
 * @since 2015-10-27 09:46:05 新建
 */
public final class BoundSqlUtil {

    // private static final Logger logger = LoggerFactory.getLogger(BoundSqlUtil.class);

    /**
     * <dl>
     * <dt><b> 从 {@link BoundSql} 中解析SQL语句 </b></dt>
     * <p>
     * <dd>功能描述</dd>
     * </dl>
     *
     * @param boundSql {@linkplain BoundSql MyBatis动态SQL语句}
     * @return SQL语句
     * @author 李远明
     */
    public static String getConfigSql(BoundSql boundSql) {
        String sql = boundSql.getSql();
        sql = sql.replaceAll("[\\s]+", " ");
        return sql;
    }

    /**
     * <dl>
     * <dt><b> 解析MyBatis配置文件中的SQL查询语句，构造查询数据总条数语句。 </b></dt>
     * <p>
     * <dd>SELECT COUNT(1) AS COUNT1 FROM xxx</dd>
     * </dl>
     *
     * @param configuration MyBatis配置
     * @param boundSql      封装的SQL语句
     * @return COUNT SQL
     * @author 李远明
     * @version 2015-11-04 23:30:16 新建。这个方法先使用 {@link #getConfigSql(BoundSql)}方法得到带问号的查询语句，再使用
     * {@link #buildCountSql(String)}方法将查询语句转化为COUNT语句，最后将COUNT语句中的问号替换为实际的值。
     * <p>
     * 因为Oracle中的值分大小写。
     */
    public static String buildCountSql(Configuration configuration, BoundSql boundSql) {
        String querySql = getConfigSql(boundSql);
        String countSql = buildCountSql(querySql);

        // XXX 将 ? 替换为对应的值。2015-11-05 13:32
        Object parameterObject = boundSql.getParameterObject(); // 查询的实体
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        if (parameterMappings.size() > 0 && parameterObject != null) {
            TypeHandlerRegistry thRegistry = configuration.getTypeHandlerRegistry();
            if (thRegistry.hasTypeHandler(parameterObject.getClass())) {
                countSql = countSql.replaceFirst("\\?", valueWrapper(parameterObject));
            } else {
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String name = parameterMapping.getProperty();
                    if (metaObject.hasGetter(name)) {
                        Object value = metaObject.getValue(name);
                        countSql = countSql.replaceFirst("\\?", valueWrapper(value));
                    } else if (boundSql.hasAdditionalParameter(name)) {
                        Object value = boundSql.getAdditionalParameter(name);
                        countSql = countSql.replaceFirst("\\?", valueWrapper(value));
                    }
                }
            }
        }
        return countSql;
    }

    /**
     * <dl>
     * <dt><b> 包装值 </b></dt>
     * <p>
     * <dd></dd>
     * </dl>
     *
     * @param value 原始值
     * @return 返回值，使用单引号括住
     * @author 李远明
     */
    private static String valueWrapper(Object value) {
        String retValue = null;
        if (value instanceof String) {
            retValue = "'" + value + "'";
        } else if (value instanceof java.util.Date) {
            DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            retValue = "'" + formatter.format(value) + "'";
        } else {
            if (value != null) {
                retValue = value.toString();
            } else {
                retValue = "";
            }
        }
        return retValue;
    }

    /**
     * <DL>
     * <DT><B> 根据原始的查询SQL语句拼接查询数据总条数的SQL语句 </B></DT>
     * <p>
     * <DD>详细介绍</DD>
     * </DL>
     * <p>
     *
     * @param querySql 查询SQL
     * @return 分页SQL SELECT COUNT(1) AS COUNT1 FROM XX
     * @version 2014-10-16 11:43:53 新建
     */
    public static String buildCountSql(String querySql) {
        querySql = " " + querySql;
        querySql = querySql.replace("\t", " ").replace("(", " ( ").replace(")", " ) ");

        querySql = querySql.toUpperCase();
        int index = querySql.lastIndexOf(" ORDER ");
        if (index != -1) {
            querySql = querySql.substring(0, index);
        }

        if ((querySql.contains(" UNION ")) || (querySql.contains(" GROUP "))
                || (querySql.contains(" DISTINCT "))) {
            return "SELECT COUNT(1) AS COUNT1 FROM (" + querySql + ") AS TMP";
        }
        return processCommonSql(querySql);
    }

    /**
     * <DL>
     * <DT><B> 根据原始的查询SQL语句拼接查询数据总条数的SQL语句 </B></DT>
     * <p>
     * <DD>详细介绍</DD>
     * </DL>
     * <p>
     *
     * @param querySql 普通的查询语句，没有连接等。
     * @return
     * @version 2014-10-16 11:44:45
     */
    private static String processCommonSql(String querySql) {
        // sql = sql.toUpperCase();

        int selectCount = 0;
        int selectIndex = querySql.indexOf(" SELECT ");
        if (selectIndex != -1) {
            selectCount++;
        } else {
            throw new BadSqlGrammarException("Count SQL", querySql, new SQLException("unsupported sql"));
        }

        int fromIndex = querySql.indexOf(" FROM ");
        selectIndex = querySql.indexOf(" SELECT ", selectIndex + 1);
        while (selectCount != 0) {
            if ((selectIndex != -1) && (selectIndex < fromIndex)) {
                selectCount++;
                selectIndex = querySql.indexOf(" SELECT ", selectIndex + 1);
                continue;
            }
            selectCount--;
            if (selectCount != 0) {
                fromIndex = querySql.indexOf(" FROM ", fromIndex + 1);
            }
        }

        String countSql = null;
        if (fromIndex != -1) {
            String from = querySql.substring(fromIndex);
            countSql = "SELECT COUNT(1) AS COUNT1" + from;
        } else {
            throw new BadSqlGrammarException("Count SQL", querySql, new SQLException("unsupported sql"));
        }
        return countSql;
    }

    public static void main(String[] args) {
        Calendar c = Calendar.getInstance();
        c.set(Calendar.YEAR, 2002);
        c.set(Calendar.MONTH, 11); // 2002-12-18
        c.set(Calendar.DAY_OF_MONTH, 18);
        java.util.Date date = c.getTime();
        System.out.println(date);
        System.out.println(valueWrapper(date));
    }

}
